package edu.mis.modules.accountingReports;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class LrbController extends Controller {
	public void index() {
		render("lrb.html");
	}

	/**
	 * 获取利润表数据
	 */
	public void get() {
		try {
			JSONArray array = new JSONArray();
			String date = getPara("date");
			Map<String, Object> xm = null;
			List<Record> list = null;
			// 一、营业收入
			double ljYysr = 0, byYysr = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "一、营业收入");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6001,6051)");
			ljYysr = list.get(0).getDouble("ljdf") + list.get(1).getDouble("ljdf");
			byYysr = list.get(0).getDouble("dffs") + list.get(1).getDouble("dffs");
			xm.put("lj", ljYysr);
			xm.put("by", byYysr);
			array.add(0, xm);
			// 营业成本
			double ljYycb = 0, byYycb = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：营业成本");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6401,6402)");
			ljYycb = list.get(0).getDouble("ljjf") + list.get(1).getDouble("ljjf");
			byYycb = list.get(0).getDouble("jffs") + list.get(1).getDouble("jffs");
			xm.put("lj", ljYycb);
			xm.put("by", byYycb);
			array.add(1, xm);
			// 营业税金及附加
			double ljYysj = 0, byYysj = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：营业税金及附加");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6403)");
			ljYysj = list.get(0).getDouble("ljjf");
			byYysj = list.get(0).getDouble("jffs");
			xm.put("lj", ljYysj);
			xm.put("by", byYysj);
			array.add(2, xm);
			// 销售费用
			double ljXsfy = 0, byXsfy = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：销售费用");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6601)");
			ljXsfy = list.get(0).getDouble("ljjf");
			byXsfy = list.get(0).getDouble("jffs");
			xm.put("lj", ljXsfy);
			xm.put("by", byXsfy);
			array.add(3, xm);
			// 管理费用
			double ljGlfy = 0, byGlfy = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：管理费用");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6602)");
			ljGlfy = list.get(0).getDouble("ljjf");
			byGlfy = list.get(0).getDouble("jffs");
			xm.put("lj", ljGlfy);
			xm.put("by", byGlfy);
			array.add(4, xm);
			// 财务费用
			double ljCwfy = 0, byCwfy = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：财务费用");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6603)");
			ljCwfy = list.get(0).getDouble("ljjf");
			byCwfy = list.get(0).getDouble("jffs");
			xm.put("lj", ljCwfy);
			xm.put("by", byCwfy);
			array.add(5, xm);
			// 加：投资收益
			double ljTzsy = 0, byTzsy = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "加：投资收益");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6111)");
			ljTzsy = list.get(0).getDouble("ljdf");
			byTzsy = list.get(0).getDouble("dffs");
			xm.put("lj", ljTzsy);
			xm.put("by", byTzsy);
			array.add(6, xm);

			// 二、营业利润
			double ljYylr = ljYysr - ljYycb - ljYysj - ljXsfy - ljGlfy - ljCwfy + ljTzsy,
					byYylr = byYysr - byYycb - byYysj - byXsfy - byGlfy - byCwfy + byTzsy;
			xm = new HashMap<String, Object>();
			xm.put("name", "二、营业利润（亏损以“-”号填列）");
			xm.put("lj", ljYylr);
			xm.put("by", byYylr);
			array.add(7, xm);
			// 加：营业外收入
			double ljYywsr = 0, byYywsr = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "加：营业外收入");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6301)");
			ljYywsr = list.get(0).getDouble("ljdf");
			byYywsr = list.get(0).getDouble("dffs");
			xm.put("lj", ljYywsr);
			xm.put("by", byYywsr);
			array.add(8, xm);
			// 减：营业外支出
			double ljYywzc = 0, byYywzc = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：营业外支出");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6711)");
			ljYywzc = list.get(0).getDouble("ljjf");
			byYywzc = list.get(0).getDouble("jffs");
			xm.put("lj", ljYywzc);
			xm.put("by", byYywzc);
			array.add(9, xm);

			// 三、利润总额
			double ljLrze = ljYylr + ljYywsr - ljYywzc, byLrze = byYylr + byYywsr - byYywzc;
			xm = new HashMap<String, Object>();
			xm.put("name", "三、利润总额（亏损总额以“-”号填列）");
			xm.put("lj", ljLrze);
			xm.put("by", byLrze);
			array.add(10, xm);
			// 减：所得税费用
			double ljSdsfy = 0, bySdsfy = 0;
			xm = new HashMap<String, Object>();
			xm.put("name", "减：所得税费用");
			list = Db.find("select * from kmye where date_format(date,'%Y-%m')='" + date
					+ "' and isInit = 0 and captionId in (6801)");
			ljSdsfy = list.get(0).getDouble("ljjf");
			bySdsfy = list.get(0).getDouble("jffs");
			xm.put("lj", ljSdsfy);
			xm.put("by", bySdsfy);
			array.add(11, xm);

			// 四、净利润
			double ljJlr = ljLrze - ljSdsfy, byJlr = byLrze - bySdsfy;
			xm = new HashMap<String, Object>();
			xm.put("name", "四、净利润（净亏损以“-”号填列）");
			xm.put("lj", ljJlr);
			xm.put("by", byJlr);
			array.add(12, xm);

			renderJson(array.toJSONString());
		} catch (Exception e) {
			// TODO: handle exception
			setAttr("success", false);
			setAttr("msg", "查询失败!");
			renderJson();
		}
	}
	
	/**
	 * 获取收支表数据
	 */
	public void getSZ() {
		try {
			JSONArray array = new JSONArray();
			String date = null;
			Map<String, Object> xm = null;
			List<Record> list = null;
			List<Record> ls = Db.find("select date from voucher group by month(date)");
			int length = ls.size();
			if(length==0){
				setAttr("success", false);
				setAttr("msg", "未查询到数据!");
				renderJson();
			}
			for(int i=0;i<length;i++){
				date = ls.get(i).get("date").toString();
				String[] strArray = date.split("-");
				String date1 = strArray[0]+"-"+strArray[1];
				xm = new HashMap<String, Object>();
				double sr = 0,fy=0;
				// 主营业务收入
				list = Db.find("SELECT sum(creditAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6001");
				if(list.get(0).get("sum(creditAmount)")==null){
					sr = sr+ 0;
				}else{
					sr = sr+ list.get(0).getDouble("sum(creditAmount)");
				}
				// 其他业务收入
				list = Db.find("SELECT sum(creditAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6051");
				if(list.get(0).get("sum(creditAmount)")==null){
					sr = sr+ 0;
				}else{
					sr = sr+ list.get(0).getDouble("sum(creditAmount)");
				}
				// 投资收益
				list = Db.find("SELECT sum(creditAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6111");
				if(list.get(0).get("sum(creditAmount)")==null){
					sr = sr+ 0;
				}else{
					sr = sr+ list.get(0).getDouble("sum(creditAmount)");
				}
				// 营业外收入
				list = Db.find("SELECT sum(creditAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6301");
				if(list.get(0).get("sum(creditAmount)")==null){
					sr = sr+ 0;
				}else{
					sr = sr+ list.get(0).getDouble("sum(creditAmount)");
				}
				// 主营业务成本
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6401");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 其他业务成本
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6402");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 营业税金及附加
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6403");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 销售费用
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6601");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 管理费用
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6602");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 财务费用
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6603");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 营业外支出
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6711");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				// 所得税费用
				list = Db.find("SELECT sum(debitAmount) from ( SELECT vi.*,v.date,ac.name FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId LEFT JOIN account_caption ac ON ac.captionId=vi.captionId) as A WHERE date_format(date,'%Y-%m')='" + date1
						+ "' and captionId = 6801");
				if(list.get(0).get("sum(debitAmount)")==null){
					fy = fy+ 0;
				}else{
					fy = fy+ list.get(0).getDouble("sum(debitAmount)");
				}
				xm.put("date", date1);
				xm.put("sr", sr);
				xm.put("fy", fy);
				xm.put("lr", sr-fy);
				array.add(i,xm);
			}
			renderJson(array.toJSONString());
		} catch (Exception e) {
			// TODO: handle exception
			setAttr("success", false);
			setAttr("msg", "查询失败!");
			renderJson();
		}
	}
}
